# -*- coding:utf-8 -*-

import os
import django


os.environ.setdefault("DJANGO_SETTINGS_MODULE", "channels_demo.settings")
django.setup()

# TimeSeconds TagID Value
dd = [
	[1378700244, 'A1', 3.75],
	[1378700245, 'A1', 30],
	[1378700304, 'A1', 1.2],
	[1378700305, 'A2', 56],
	[1378700344, 'A2', 11],
	[1378700345, 'A3', 0.53],
	[1378700364, 'A1', 4],
	[1378700365, 'A1', 14.5],
	[1378700384, 'A1', 144],
	[1378700384, 'A4', 10], ]


ss = [[1378700244, 'A1', 0.25],
	[1378700304, 'A4', 10],]
if __name__ == '__main__':
	from app.models import Table
	
	intable = []
	# for d in ss:
	# 	intable.append(Table(TimeSeconds=d[0],TagID=d[1],Value=d[2]))
	# Table.objects.bulk_create(intable)
	# Table.objects.
	
	from django.db import connection
	from django.db.models import Max
	cursor = connection.cursor()
	#获取这个光标，等待执行这个sql的语句 case when
	# cursor.execute("SELECT TimeSeconds,MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,MAX(CASE WHEN TagID = 'A4' THEN Value END) A4 FROM table1 GROUP BY TimeSeconds")
	# cursor.execute("SELECT TimeSeconds,COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 0) A1,COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 0) A2,COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 0) A3,COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 0) A4 FROM table1 GROUP BY TimeSeconds")
	cursor.execute(
		"SELECT TimeSeconds,COALESCE(SUM(CASE WHEN table1.clas = 1 THEN num END), 0) a1,COALESCE(SUM(CASE WHEN clas = 2 THEN num END), 0) A2,COALESCE(SUM(CASE WHEN clas = 3 THEN num END), 0) a3,COALESCE(SUM(CASE WHEN clas = 4 THEN num END), 0) a4 FROM table1 GROUP BY table1.TimeSeconds")
	
	# cursor.execute("select TineSeconds,")
	row = cursor.fetchall()
	# print(row)
	for i in row:
		print(i)
	# out = Table.objects.values('TimeSeconds').values("TagID").annotate(Max("Value"))
	# print(out)
	
	# (1378700244, 3.75, 0.0, 0.0, 0.0)
	# (1378700245, 30.0, 0.0, 0.0, 0.0)
	# (1378700304, 1.2, 0.0, 0.0, 0.0)
	# (1378700305, 0.0, 56.0, 0.0, 0.0)
	# (1378700344, 0.0, 11.0, 0.0, 0.0)
	# (1378700345, 0.0, 0.0, 0.53, 0.0)
	# (1378700364, 4.0, 0.0, 0.0, 0.0)
	# (1378700365, 14.5, 0.0, 0.0, 0.0)
	# (1378700384, 144.0, 0.0, 0.0, 10.0)
	
	# (1378700244, 3.75, 0.0, 0.0, 0.0)
	# (1378700245, 30.0, 0.0, 0.0, 0.0)
	# (1378700304, 1.2, 0.0, 0.0, 0.0)
	# (1378700305, 0.0, 56.0, 0.0, 0.0)
	# (1378700344, 0.0, 11.0, 0.0, 0.0)
	# (1378700345, 0.0, 0.0, 0.53, 0.0)
	# (1378700364, 4.0, 0.0, 0.0, 0.0)
	# (1378700365, 14.5, 0.0, 0.0, 0.0)
	# (1378700384, 156.0, 0.0, 0.0, 30.0)